﻿CREATE proc NKNH_XuatBangExCel  --'30/09/2013'        
--NKNH_XuatBangExCel '2013/09/30'        
@ngay_ctu datetime        
as                 
begin                    
   -- select*from NKNH_H         
   -- select*from nknh_l       
    
         
   begin               
  select distinct(h.so_ctu),h.ngay_ctu,h.hoten ,h.tk_vatc,                
  h.noi_dung,l.so_tien,l.ngoaite,l.tien_vat ,l.nt_vat,h.tk_vat,h.loai_tien,h.ty_gia       
  ,case    
   when ISNULL(h.tk_no,'')='' then l.tk    
   else    
   h.tk_no   
   end   
    as tk_no   
    
     ,case    
   when ISNULL(h.nosap,'')='' then l.tk_sap    
   else    
   h.nosap   
   end   
    as nosap
    
      ,case    
   when ISNULL(h.chitiet_tknosap,'')='' then l.chitiet_tksap    
   else    
   h.chitiet_tknosap   
   end   
    as no_sap
     
    ,case    
   when ISNULL(h.chitiet_tkno,'')='' then l.chitiet_tk    
   else    
   h.chitiet_tkno   
   end   
    as chitiet_tkno 
    
    ,case    
   when ISNULL(h.cosap,'')='' then l.tk_sap   
   else    
   h.cosap    
   end     
   as cosap   
     
     ,case    
   when ISNULL(h.tk_co,'')='' then l.tk    
   else    
   h.tk_co    
   end     
   as tk_co   
      
   ,case    
   when ISNULL(h.chitiet_tkco,'')='' then l.chitiet_tk    
   else    
   h.chitiet_tkco   
   end   
    as chitiet_co  
    
    ,case    
   when ISNULL(h.chitiet_tkcosap,'')='' then l.chitiet_tksap   
   else    
   h.chitiet_tkcosap    
   end     
   as co_sap 
    
    
               
   from NKNH_H h inner join NKNH_L l on h.NHID=l.NHID                
          
   where  h.ngay_ctu=@ngay_ctu and (h.tk_co in('1121','1122') or h.tk_no in('1121','1122'))    
   end                
end 